const db = require('../common/mysql')

function queryAll () {
  return new Promise((resolve, reject) => {
    // 执行一个查询
    db.query('select * from user_tel', function (error, results) {
      if (error) reject(error)
      resolve(results)
    })
  })
}

function queryDemand (pageNo, pageSize) {
  return new Promise((resolve, reject) => {
    const jsonResult = {}
    // 执行一个查询
    db.connPool.query('select t.id,t.username,t.tel,t.create_time,(select max(create_time) from user_tel where username=t.username) lately_time,(select count(0) from user_tel where username=t.username) sl,' +
        '(select count(0)+1 from user_tel where create_time<t.create_time and DATE_FORMAT(create_time,\'%y-%m-%d\')=DATE_FORMAT(t.create_time,\'%y-%m-%d\') and username=t.username ) cs' +
        ' from user_tel t order by t.create_time limit ?,? ', [(pageNo - 1) * 2, pageSize], function (error, results) {
      if (error) reject(error)
      jsonResult.pageNo = pageNo
      jsonResult.results = results
      resolve(jsonResult)
    })
    /* conn.query("select t.id,t.username,t.tel,t.create_time,max( t.create_time ) over (PARTITION BY t.username) lately_time "+
          ",ROW_NUMBER() over (PARTITION BY DATE_FORMAT(t.create_time,'%y-%m-%d') order by t.create_time) cs,count(0) over (PARTITION BY t.username) sl"+
          "from user_tel t order by t.create_time",[pageNo,pageSize], function (error, results) {
           if (error) reject(error)
           jsonResult.pageNo=pageNo;
           jsonResult.results=results;
           resolve(jsonResult)
        })    */
  })
}

function queryCount () {
  return new Promise((resolve, reject) => {
    db.connPool.query('select count(0) sl from user_tel', function (error, results) {
      if (error) reject(error)
      resolve(results[0])
    })
  })
}

function queryById (id) {
  return new Promise(function (resolve, reject) {
    db.connPool.query('select * from user_tel where id=?', [id], function (error, results, fields) {
      if (error) reject(error)
      resolve(results)
    })
  })
}
/*
async function testTransaction () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  const conn = await db.getConn()
  conn.beginTransaction(function (err) {
    if (err) { throw err }
    conn.query(sql, ['u3', '113'], function (error, results, fields) {
      if (error) {
        return conn.rollback(function () {
          throw error
        })
      }

      console.log('do 1')
      // const log = 'Post ' + results.insertId + ' added'

      conn.query(sql, ['u4', '114'], function (error, results, fields) {
        if (error) {
          return conn.rollback(function () {
            throw error
          })
        }
        conn.commit(function (err) {
          if (err) {
            return conn.rollback(function () {
              throw err
            })
          }
          console.log('success!')
        })
      })
    })
  })
}
testTransaction()
*/
/* async function testTransaction1 () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  let conn
  try {
    // 1. 获取连接
    conn = await db.getConn()
    // 2.开启事务
    await db.beginTransaction(conn)
    // 3. 执行业务单元
    const r1 = await db.query(conn, sql, ['u6', '116'])
    console.log(r1)
    const r2 = await db.query(conn, sql, ['u7', '117'])
    console.log(r2)
    // throw new Error('错误！')
    // 4. 提交
    await db.commit(conn)
    console.log('commit')
  } catch (error) {
    // 5. 回滚
    if (conn) {
      db.rollback(conn)
      console.log('roolback', error)
    }
  }
}
testTransaction1() */

/*  async function testTransaction2 () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  db.transaction(async (conn) => {
    // 3. 执行业务单元
    const r1 = await db.query(conn, sql, ['u8', '118'])
    console.log(r1)
    const r2 = await db.query(conn, sql, ['u9', '119'])
    console.log(r2)
    throw Error('错误！')
  })
}
testTransaction2()  */

module.exports = {
  queryAll,
  queryById,
  queryDemand,
  queryCount
}
